import pymysql
import pymssql
from pymssql import _mssql
from pymssql import _pymssql
import uuid
import decimal

# mysql连接
def get_mysql_conn(host, port, user, password, database):
    # 获取mysql连接
    return pymysql.connect(host=host, port=port, user=user, password=password, database=database, charset="utf8")

# sql server连接
def get_sql_server_conn(host, port, user, password, database):
    # 获取sql server连接
    return pymssql.connect(host=host, port=port, user=user, password=password, database=database, charset="utf8", tds_version="7.0")

# 获取sql server数据
def get_server_data(conn, fields, where, table):
    sql = "select %s from %s where %s" % (fields, table, where)
    print(sql)
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    return result

# 获取sql server数据
def get_server_data_car(conn, sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    return result
# 获取一条信息
def get_server_data_one(conn, sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    return result

# 获取所有字段
def get_index_dict(cursor):
    index_dict=dict()
    index=0
    for desc in cursor.description:
        index_dict[desc[0]]=index
        index=index+1
    return index_dict

# 转字典
def get_dict_data_sql(conn,sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    data=cursor.fetchall()
    index_dict=get_index_dict(cursor)
    res=[]
    for datai in data:
        resi=dict()
        for indexi in index_dict:
            resi[indexi]=datai[index_dict[indexi]]
        res.append(resi)
    return res

# 写入mysql数据
def put_mysql_data(conn, fields, table, data):
    sql = "insert into %s (%s) values" % (table, fields)
    cursor = conn.cursor()
    count = fields.count(",")
    values = ""
    for i in range(0, count + 1):
        values += "%s,"
    values = values[0:-1]
    cursor.executemany(sql + "(" + values + ")", data)
    conn.commit()
    print (sql)
    lines = cursor.rowcount
    return lines

# 执行一条sql语句
def carried_sql(conn, sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    return cursor.lastrowid